package com.b2c.repository.oms;

import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.DateUtil;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.apierp.req.ErpSalesOrderRefundReq;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.ErpOrderEntity;
import com.b2c.entity.ErpOrderItemEntity;
import com.b2c.entity.ErpOrderReturnEntity;
import com.b2c.entity.erp.enums.ErpOrderSourceEnum;
import com.b2c.entity.erp.enums.ErpStocktakingInvoiceStatusEnum;
import com.b2c.entity.pdd.OrderPddEntity;
import com.b2c.entity.pdd.OrderPddItemEntity;
import com.b2c.entity.pdd.RefundPddEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.enums.erp.EnumGoodsStockLogSourceType;
import com.b2c.repository.Tables;
import com.b2c.entity.enums.erp.EnumOrderReturnStatus;

import com.b2c.repository.utils.OrderNumberUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 拼多多订单Repository
 */
@Repository
public class OrderPddRefundRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询订单信息
     * @param pageIndex
     * @param pageSize
     * @param refundId 退款id
     * @param orderSn
     * @param status
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Transactional
    public PagingResponse<RefundPddEntity> getList(Integer pageIndex, Integer pageSize, String num, Integer status,Integer auditStatus,Integer shippingStatus, Integer shopId,Integer afterSalesType,Long goodsId) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS o.*,orders.order_status,pgs.spec as GoodsSpec,eg.`name` as erpGoodsName,shop.name as shopName   ");
        sb.append(",IFNULL((SELECT status FROM erp_order_send_return WHERE sourceNo=o.id),88) as orderReturnStatus ");
        sb.append(" FROM ").append(Tables.DcPddOrderRefund).append(" o ");
        sb.append(" LEFT JOIN ").append(Tables.DcPddOrder).append(" orders on orders.order_sn=o.order_sn ");
        sb.append(" LEFT JOIN dc_shop_goods pg on pg.goodsId=o.goods_id ");
        sb.append(" LEFT JOIN erp_goods eg on eg.id = pg.erp_goods_id ");
        sb.append(" LEFT JOIN dc_shop_goods_sku pgs on pgs.skuId=o.skuId ");
        sb.append(" LEFT JOIN dc_shop shop on shop.id=o.shopId ");

        sb.append(" WHERE 1=1 ");

        if(shopId!=null && shopId.intValue() >0){
            sb.append(" AND o.shopId = ? ");
            params.add(shopId);
        }


        if (!StringUtils.isEmpty(status) && status>-1) {
            sb.append(" AND o.after_sales_status = ? ");
            params.add(status);
        }
        if(shippingStatus!=null){
            sb.append(" AND o.shipping_status = ? ");
            params.add(shippingStatus);
        }

        if (auditStatus!=null && auditStatus>-1) {
            sb.append(" AND o.auditStatus = ? ");
            params.add(auditStatus);
        //    if(auditStatus == 1){
        //        sb.append("AND o.auditStatus > 0");
        //    }else if(auditStatus == 0){
        //     sb.append(" AND o.auditStatus = 0 ");
        //     // params.add(auditStatus);
        //    }
        }
        

//        if (!StringUtils.isEmpty(status) && status>-1) {
//            if(status.intValue()==2){
//                sb.append(" AND (o.auditStatus = 0  AND LENGTH(o.tracking_number)>0 ) ");
//            }else {
//                sb.append(" AND o.auditStatus = ? ");
//                params.add(status);
//            }
//        }
        if (afterSalesType != null && afterSalesType > 0) {
            if(afterSalesType.intValue() == 9){
                //已发货仅退款
                sb.append(" AND o.after_sales_type = 2 AND orders.order_status > 1 ");
            }else{
                sb.append(" AND o.after_sales_type = ? ");
                params.add(afterSalesType);
            }
            
        }
        // if (StringUtils.isEmpty(orderSn) == false) {
        //     sb.append(" AND o.order_sn like ? ");
        //     params.add("%" + orderSn + "%");
        // }
        if (goodsId != null && goodsId > 0) {
            sb.append(" AND o.goods_id = ? ");
            params.add(goodsId);
        }
        // if(!StringUtils.isEmpty(logisticsCode)){
        //     sb.append(" AND o.tracking_number = ? ");
        //     params.add(logisticsCode);
        // }
        // if (startTime != null && startTime > 0) {
        //     sb.append(" AND o.created_time >= ? ");
        //     params.add(startTime);
        // }

        // if (endTime != null && endTime > 0) {
        //     sb.append(" AND o.created_time <= ? ");
        //     params.add(endTime);
        // }
        if (StringUtils.isEmpty(num) == false) {
            sb.append(" AND (o.order_sn = ? OR o.id = ? OR o.tracking_number = ? )");
            params.add(num);
            params.add(num);
            params.add(num);
        }
        // if(StringUtils.hasText(orderSn) ||StringUtils.hasText(logisticsCode) || refundId != null || status != null || startTime != null ||endTime!=null||afterSalesType!=null){
            
        // }else{
        //     if(auditStatus == null) sb.append(" AND (o.auditStatus = 0 OR  o.auditStatus = 2) ");

        // }

        sb.append(" ORDER BY o.auditTime desc,o.created_time desc ");


//        if(auditStatus == 0 && afterSalesType == 3){
//            var listAll = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(RefundPddEntity.class), params.toArray(new Object[params.size()]));
//            String sql = "SELECT COUNT(0) FROM erp_order_return WHERE source_order_num=? AND shopId=18";
//            for (var re:listAll) {
//               var isR = jdbcTemplate.queryForObject(sql, int.class,re.getOrder_sn());
//               if(isR>0){
//                   jdbcTemplate.update("UPDATE dc_pdd_refund SET auditStatus=1 WHERE id=?",re.getId());
//               }
//            }
//        }

        sb.append("  LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(RefundPddEntity.class), params.toArray(new Object[params.size()]));

        int totalSize = getTotalSize();



        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     *
     * @param id
     * @return
     */
    public RefundPddEntity getEntityById(Long id){
        try{
            var refund = jdbcTemplate.query("SELECT * FROM "+Tables.DcPddOrderRefund+" WHERE id=? ",new BeanPropertyRowMapper<>(RefundPddEntity.class),id);
            if(refund!=null && refund.size()>0)return refund.get(0);
            return null;
        } catch (Exception e){
            return null;
        }
    }
    public RefundPddEntity getEntityByOrderSn(String orderSn){
        try{
            var refund = jdbcTemplate.query("SELECT * FROM "+Tables.DcPddOrderRefund+" WHERE order_sn=?  ORDER BY id desc",new BeanPropertyRowMapper<>(RefundPddEntity.class),orderSn);
            if(refund!=null && refund.size()>0)return refund.get(0);
            return null;
        } catch (Exception e){
            return null;
        }
    }

    @Transactional
    public void signRefund(Long id,Integer auditStatus,String remark) {
        if(StringUtils.isEmpty(remark)) remark = "";

        RefundPddEntity refund = jdbcTemplate.queryForObject("SELECT * from dc_pdd_refund where id=?", new BeanPropertyRowMapper<>(RefundPddEntity.class), id);

        if(StringUtils.hasText(refund.getDescribe())) remark = refund.getDescribe() + remark;
        

        jdbcTemplate.update("UPDATE "+Tables.DcPddOrderRefund+" SET auditStatus=?,sign=?,auditTime=?,`describe`=? WHERE id=?",
        auditStatus,
        "人工标记为["+auditStatus+"]"+DateUtil.getCurrentDateTime(),DateUtil.getCurrentDateTime(),
        remark,
        id);

        //如果是签收，下发到仓库系统
        if(auditStatus.intValue() == 2){


            // 2是已签收，下发到仓库系统
            String sql = "SELECT * FROM dc_pdd_orders_item oi LEFT JOIN dc_pdd_orders o on o.id=oi.order_id WHERE o.order_sn = ? ";
            var orderItem = jdbcTemplate.queryForObject(sql,
                        new BeanPropertyRowMapper<>(OrderPddItemEntity.class),
                        refund.getOrder_sn());
            Integer returnType = 1;
            if(refund.getAfter_sales_type().intValue() == 4){
                returnType =2;//换货
            }else if(refund.getAfter_sales_type().intValue() == 9){
                returnType =9;//拦截退货
            }
            String s = "INSERT INTO erp_order_send_return (orderSn,shopId,goodsId,goodsSpecId,goodsSpecNumber,logisticsCode,quantity,refundAmount,receiveTime,returnType,sourceNo) VALUE (?,?,?,?,?,?,?,?,?,?,?)";
            jdbcTemplate.update(s, refund.getOrder_sn(), refund.getShopId(),
                        orderItem.getErpGoodsId(), orderItem.getErpGoodsSpecId(),
                        orderItem.getGoodsSpecNum(),
                        refund.getTracking_number(), refund.getQuantity(), refund.getRefund_amount(),
                        DateUtil.getCurrentDateTime(),returnType,refund.getId());
        }
        // TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
    }

    /**
     * 确认退货并到仓库
     *
     * @param id
     * @param
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Long> confirmRefund(Long id) {
        try {
            //查询退款单
            String sql = "SELECT * FROM " + Tables.DcPddOrderRefund + " WHERE id=? ";
            RefundPddEntity refundOrder = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(RefundPddEntity.class), id);

            if (refundOrder == null) return new ResultVo<>(EnumResultVo.NotFound, "退货订单不存在");//订单不存在return 404;
            if (refundOrder.getAuditStatus().intValue() == 1) {
                return new ResultVo<>(EnumResultVo.HasAssociatedData, "已经处理过了，不能重复处理");
            }
            //if(refundOrder.getAfter_sales_type() !=3 ) return new ResultVo<>(EnumResultVo.HasAssociatedData, "不是退货退款订单，不能处理");

            /*********查询对应的订单pdd_order *********/
            OrderPddEntity order = null;
            OrderPddItemEntity orderItem = null;
            try {
                //查询关联的订单
                order = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.DcPddOrder + " WHERE order_sn=?", new BeanPropertyRowMapper<>(OrderPddEntity.class), refundOrder.getOrder_sn());

                orderItem = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.DcPddOrderItem+" WHERE order_id=?",new BeanPropertyRowMapper<>(OrderPddItemEntity.class),order.getId());
                
            } catch (Exception e) {
                return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");//订单不存在return 404;
            }



            Integer goodsSpecId = 0;
            //查询erp商品规格信息
            try {
                goodsSpecId = jdbcTemplate.queryForObject("SELECT id FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? limit 1", Integer.class, refundOrder.getSkuNumber());
            } catch (Exception E) {
                return new ResultVo<>(EnumResultVo.NotFound, "SKU不存在");//订单不存在return 404;
            }

            //查询仓库对应的订单
            ErpOrderEntity erpOrder= null;
            ErpOrderItemEntity erpOrderItem = null;
            try{
                erpOrder = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.ErpOrder +" WHERE order_num=?",new BeanPropertyRowMapper<>(ErpOrderEntity.class), order.getOrderSn());
                erpOrderItem = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.ErpOrderItem+" WHERE orderId=? AND skuId=?",new BeanPropertyRowMapper<>(ErpOrderItemEntity.class), erpOrder.getId(),goodsSpecId);
            }catch(Exception e1){
                return new ResultVo<>(EnumResultVo.NotFound, "仓库系统中找不到对应的订单");
            }

            /***********  查询仓库系统退货订单 是否存在 *************/
            var erpOrderReturn = jdbcTemplate.query("SELECT * FROM " + Tables.ErpOrderReturn + " WHERE order_num=? and  shopId=?", new BeanPropertyRowMapper<>(ErpOrderReturnEntity.class),refundOrder.getId(),refundOrder.getShopId());
            if (erpOrderReturn != null && erpOrderReturn.size() > 0) {
                //退货单已经存在
                return new ResultVo<>(EnumResultVo.Fail, "仓库已经存在该退货单");
            }
            erpOrderReturn = jdbcTemplate.query("SELECT * FROM "+Tables.ErpOrderReturn +" WHERE source_order_num=? AND shopId=?", new BeanPropertyRowMapper<>(ErpOrderReturnEntity.class),refundOrder.getOrder_sn(),refundOrder.getShopId());
            if (erpOrderReturn != null && erpOrderReturn.size() > 0) {
                //退货单已经存在
                return new ResultVo<>(EnumResultVo.Fail, "仓库已经存在该退货单");
            }
//            erpOrderReturn = jdbcTemplate.query("SELECT * FROM "+Tables.ErpOrderReturn +" WHERE logisticsCode=? AND shopId=?", new BeanPropertyRowMapper<>(ErpOrderReturnEntity.class),refundOrder.getTracking_number(),refundOrder.getShopId());
//            if (erpOrderReturn != null && erpOrderReturn.size() > 0) {
//                //退货单已经存在
//                return new ResultVo<>(EnumResultVo.Fail, "仓库已经存在该退货单");
//            }

            Integer skuCount = jdbcTemplate.queryForObject("SELECT count(0) FROM erp_order_return_item i LEFT JOIN erp_order_return r ON i.orderId=r.id WHERE  r.source_order_num=?  AND i.skuId=?",Integer.class,refundOrder.getOrder_sn(),goodsSpecId);

//            Integer skuCount1 = jdbcTemplate.queryForObject("SELECT count(0) FROM erp_order_return_item i LEFT JOIN erp_order_return r ON i.orderId=r.id WHERE  r.logisticsCode=?  AND i.skuId=?",Integer.class,refundOrder.getTracking_number(),goodsSpecId);

//            if(skuCount.intValue()>0 || skuCount1.intValue()>0)return new ResultVo<>(EnumResultVo.Fail, "订单商品已经退货");
            if(skuCount.intValue()>0 )return new ResultVo<>(EnumResultVo.Fail, "订单商品已经退货");

            /***********  插入仓库系统退款订单 erp_order_return    erp_order_return_item  *************/
            //插入仓库退货订单 erp_order_return
            String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                    " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num,shopId)" +
                    " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            OrderPddEntity finalOrder = order;
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(returnOrder, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, String.valueOf(refundOrder.getId()));
                    ps.setObject(2, refundOrder.getCreated_time());
                    ps.setLong(3, System.currentTimeMillis() / 1000);
                    ps.setString(4, "");
                    ps.setString(5, "");
                    ps.setString(6, "");
                    ps.setString(7, ErpOrderSourceEnum.PDD.getIndex());
                    ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
                    ps.setString(9, refundOrder.getTracking_company());
                    ps.setString(10, "");
                    ps.setString(11, refundOrder.getTracking_number());
                    ps.setString(12, refundOrder.getOrder_sn());
                    ps.setInt(13, finalOrder.getShopId());
                    return ps;
                }
            }, keyHolder);
            Long returnOrderId = keyHolder.getKey().longValue();

            // Long stockInfoItemId =jdbcTemplate.queryForObject("SELECT IFNULL((SELECT stockInfoItemId  FROM erp_order_item i LEFT JOIN erp_order e ON i.orderId=e.id WHERE e.order_num=? AND  i.skuId=? LIMIT 1),0) id",Long.class,order.getOrderSn(),goodsSpecId);

            //插入仓库退货订单明细 erp_order_return_item
            String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity,refundAmount,orderItemId) VALUE (?,?,?,?,?,?,?,?,?,?,?,?)";

            jdbcTemplate.update(returnOrderItemSQL, returnOrderId, refundOrder.getGoodsId(),refundOrder.getSkuId(),refundOrder.getSkuNumber(), refundOrder.getQuantity(), 0, System.currentTimeMillis() / 1000, 0, 0, 0,refundOrder.getRefund_amount(),erpOrderItem.getId());

            /***********更新退款订单状态***********/
            jdbcTemplate.update("UPDATE "+Tables.DcPddOrderRefund+" SET auditStatus=1 WHERE id=?",id);

            return new ResultVo<>(EnumResultVo.SUCCESS, "成功",returnOrderId);

        } catch (Exception ex) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "同意退货处理失败");
        }

    }
    public ResultVo<Long> updRefundPddSpec(Long id, Integer erpGoodSpecId, Integer quantity){

        if(erpGoodSpecId==null || erpGoodSpecId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodSpecId");
        }
        if(quantity==null || quantity.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少quantity");
        }
        try {
            var order = getEntityById(id);
            if(order.getAuditStatus().intValue()!=0)
                return new ResultVo<>(EnumResultVo.DataError,"退货单已经处理");
            /**************************/
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from "+Tables.ErpGoodsSpec+" s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpOrderSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),erpGoodSpecId);

            String skuInfo = erpOrderSpec.getSizeValue()+" "+erpOrderSpec.getColorValue();

            jdbcTemplate.update("UPDATE dc_pdd_refund SET goods_id=?,skuId=?,sku_number=?,sku_info=?,quantity=? WHERE id=?",erpOrderSpec.getGoodsId(),erpOrderSpec.getId(),erpOrderSpec.getSpecNumber(),skuInfo,quantity,id);

            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，不存在orderItem");
        }
    }
    /**
     * 批量确认退货
     * @param startDate
     * @param endDate
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> confirmBatchRefund(Integer shopId,String startDate,String endDate) {
        //查询退款单
        String sql = "SELECT * FROM " + Tables.DcPddOrderRefund + " WHERE shopId=? and after_sales_type=3 and (auditStatus=0 AND LENGTH(tracking_number)>0) and  (FROM_UNIXTIME(created_time,'%Y-%m-%d')>=? and FROM_UNIXTIME(created_time,'%Y-%m-%d')<=?) ";
        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(RefundPddEntity.class),shopId,startDate,endDate);
        for(var refundOrder:list){
            if(StringUtils.isEmpty(refundOrder.getSkuNumber()))continue;
            OrderPddEntity order = null;
            try {
                //查询关联的订单
                order = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.DcPddOrder + " WHERE order_sn=? limit 1", new BeanPropertyRowMapper<>(OrderPddEntity.class), refundOrder.getOrder_sn());
            } catch (Exception e) {
                continue;
            }

            //查询仓库对应的订单
            ErpOrderEntity erpOrder= null;
            try{
                erpOrder = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.ErpOrder +" WHERE order_num=?",new BeanPropertyRowMapper<>(ErpOrderEntity.class), order.getOrderSn());
            }catch(Exception e1){
                continue;
            }
            Integer goodsSpecId = 0;
            //查询erp商品规格信息
            try {
                goodsSpecId = jdbcTemplate.queryForObject("SELECT id FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? limit 1", Integer.class, refundOrder.getSkuNumber());
            } catch (Exception E) {
                continue;
            }
            Integer skuCount = jdbcTemplate.queryForObject("SELECT count(0) FROM erp_order_return_item i LEFT JOIN erp_order_return r ON i.orderId=r.id WHERE r.source_order_num=? AND i.skuId=?",Integer.class,refundOrder.getOrder_sn(),goodsSpecId);


            Integer skuCount1 = jdbcTemplate.queryForObject("SELECT count(0) FROM erp_order_return_item i LEFT JOIN erp_order_return r ON i.orderId=r.id WHERE  r.logisticsCode=?  AND i.skuId=?",Integer.class,refundOrder.getTracking_number(),goodsSpecId);

            if(skuCount.intValue()>0 || skuCount1.intValue()>0){
                jdbcTemplate.update("UPDATE dc_pdd_refund set auditStatus=1 WHERE id=?",refundOrder.getId());
                continue;
            }
            try {
                //插入仓库退货订单 erp_order_return
                String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                        " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num,shopId)" +
                        " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
                KeyHolder keyHolder = new GeneratedKeyHolder();

                OrderPddEntity finalOrder = order;
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(returnOrder, Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, String.valueOf(refundOrder.getId()));
                        ps.setLong(2, DateUtil.dateTimeToStamp(finalOrder.getCreated_time()));
                        ps.setLong(3, System.currentTimeMillis() / 1000);
                        ps.setString(4, "");
                        ps.setString(5, "");
                        ps.setString(6, "");
                        ps.setString(7, ErpOrderSourceEnum.PDD.getIndex());
                        ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
                        ps.setString(9, refundOrder.getTracking_company());
                        ps.setString(10, "");
                        ps.setString(11, refundOrder.getTracking_number());
                        ps.setString(12, refundOrder.getOrder_sn());
                        ps.setInt(13, finalOrder.getShopId());
                        return ps;
                    }
                }, keyHolder);
                Long returnOrderId = keyHolder.getKey().longValue();

                Long stockInfoItemId =jdbcTemplate.queryForObject("SELECT IFNULL((SELECT stockInfoItemId  FROM erp_order_item i LEFT JOIN erp_order e ON i.orderId=e.id WHERE e.order_num=? AND  i.skuId=? LIMIT 1),0) id",Long.class,order.getOrderSn(),goodsSpecId);

                //插入仓库退货订单明细 erp_order_return_item
                String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity,refundAmount,stockInfoItemId) VALUE (?,?,?,?,?,?,?,?,?,?,?,?)";


                jdbcTemplate.update(returnOrderItemSQL, returnOrderId, refundOrder.getGoodsId(),refundOrder.getSkuId(),refundOrder.getSkuNumber(), refundOrder.getQuantity(), 0, System.currentTimeMillis() / 1000, 0, 0, 0,refundOrder.getRefund_amount(),stockInfoItemId);

                /***********更新退款订单状态***********/
                jdbcTemplate.update("UPDATE "+Tables.DcPddOrderRefund+" SET auditStatus=1 WHERE id=?",refundOrder.getId());
            } catch (Exception ex) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                continue;
            }
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
    }
    public void updPddRefundLogisCode(Long id,String trackingNumber){
        jdbcTemplate.update("update dc_pdd_refund set tracking_number=? where id=?",trackingNumber,id);
    }

    public void updPddRefundDescribe(Long refundId, String describe) {
        try {
            // var describeT = jdbcTemplate.queryForObject("SELECT describe FROM dc_pdd_refund WHERE id=?", String.class, refundId);
            jdbcTemplate.update("update dc_pdd_refund set `describe`=?,auditTime=? where id=?",describe,DateUtil.getCurrentDateTime(),refundId);
        } catch (Exception e) {
            throw e;
        }
        
    }

    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> addPddOrderRefund(ErpSalesOrderRefundReq refund) {
        if (refund.getOrderId() == null) return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，没有订单id");
        if (refund.getItems() == null || refund.getItems().size() == 0) return new ResultVo<>(EnumResultVo.ParamsError, "参数错误,没有选择要退货的商品");
        OrderPddEntity order = null;
        try {
            order = jdbcTemplate.queryForObject("select * from " + Tables.DcPddOrder + " where id=?", new BeanPropertyRowMapper<>(OrderPddEntity.class), refund.getOrderId());
            if (order.getOrder_status().intValue() < 2) return new ResultVo<>(EnumResultVo.ParamsError, "订单已发货才能申请退货");
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.DataError, e.getMessage());
        }
        /******************开始退货数据库操作*********************/
        OrderPddEntity finalOrder = order;
        String refundNo = "SALRET" + System.nanoTime();//退货单号
        for (var returnItem : refund.getItems()) {
            var item = jdbcTemplate.queryForObject("select * from " + Tables.DcPddOrderItem + " where id=?", new BeanPropertyRowMapper<>(OrderPddItemEntity.class), returnItem.getId());
            if(StringUtils.isEmpty(item.getGoodsSpecNum()))return new ResultVo<>(EnumResultVo.ParamsError,"SKU不能为空");
            ErpGoodsSpecEntity spec=null;
            try {
                spec = jdbcTemplate.queryForObject("select * from " + Tables.ErpGoodsSpec + " WHERE specNumber=? limit 1", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getGoodsSpecNum());
            }catch (Exception e){
                return new ResultVo<>(EnumResultVo.ParamsError,item.getGoodsSpecNum()+"不存在");
            }
            Integer erpReutnCount = jdbcTemplate.queryForObject("SELECT IFNULL(SUM(i.quantity),0)  FROM erp_order_return_item i LEFT JOIN erp_order_return r ON i.orderId=r.id WHERE r.source_order_num=? AND i.skuId=? ",Integer.class,order.getOrderSn(),spec.getId());

            /*******计算可退货数量********/
            int canReturnCount = item.getQuantity().intValue() - item.getRefundCount().intValue()-erpReutnCount;
            int returnCount = returnItem.getRefundCount();//本次退货数量

            if (canReturnCount < returnCount) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return new ResultVo<>(EnumResultVo.DataError, item.getGoodsSpecNum() + "退货数量超过可退数量");
            }
        }
        try {
            /***********  插入仓库系统退款订单 erp_order_return    erp_order_return_item  *************/
            //插入仓库退货订单 erp_order_return
            String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                    " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num,shopId)" +
                    " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";

            KeyHolder erpOrderReturnkey = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(returnOrder, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, refundNo);
                    ps.setLong(2, DateUtil.dateTimeToStamp(finalOrder.getCreated_time()));
                    ps.setLong(3, System.currentTimeMillis() / 1000);
                    ps.setString(4, "");
                    ps.setString(5, "");
                    ps.setString(6, finalOrder.getProvince()+finalOrder.getCity()+finalOrder.getTown());
                    ps.setString(7, ErpOrderSourceEnum.PDD.getIndex());
                    ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
                    ps.setString(9, refund.getLogisticsCompany());
                    ps.setString(10, "");
                    ps.setString(11, refund.getLogisticsCode());
                    ps.setString(12, finalOrder.getOrderSn());
                    ps.setLong(13, finalOrder.getShopId());
                    return ps;
                }
            }, erpOrderReturnkey);
            Long returnOrderId = erpOrderReturnkey.getKey().longValue();

            //插入仓库退货订单明细 erp_order_return_item
            String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity,refundAmount,stockInfoItemId) VALUE (?,?,?,?,?,?,?,?,?,?,?,?)";

            for (var ritem : refund.getItems()) {
                //查询订单明细
                var orderItem = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.DcPddOrderItem + " WHERE id=?", new BeanPropertyRowMapper<>(OrderPddItemEntity.class), ritem.getId());

                //查询仓库系统sku
                var sku = jdbcTemplate.queryForObject("select * from " + Tables.ErpGoodsSpec + " WHERE specNumber=? limit 1", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), orderItem.getGoodsSpecNum());

                Long stockInfoItemId =jdbcTemplate.queryForObject("SELECT IFNULL((SELECT stockInfoItemId  FROM erp_order_item i LEFT JOIN erp_order e ON i.orderId=e.id WHERE e.order_num=? AND  i.skuId=? LIMIT 1),0) id",Long.class,order.getOrderSn(),sku.getId());

                /******插入仓库系统退货商品信息*******/
                jdbcTemplate.update(returnOrderItemSQL, returnOrderId, sku.getGoodsId(), sku.getId(), sku.getSpecNumber(), ritem.getRefundCount(), 0, System.currentTimeMillis() / 1000, 0, 0, 0,ritem.getRefundCount()*orderItem.getGoodsPrice(),stockInfoItemId);
                if (ritem.getRefundCount() > 0) {
                    /*************更新orderitem数据 （售后数量）*************/
                    jdbcTemplate.update("update " + Tables.DcPddOrderItem + " set refundCount=refundCount+? where id=?", ritem.getRefundCount(),ritem.getId());
                }
            }
            return new ResultVo<>(EnumResultVo.SUCCESS, "申请成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, e.getMessage());
        }
    }


    public Long getRefundIdMin() {
        return jdbcTemplate.queryForObject("SELECT IFNULL(MAX(id),0) FROM  dc_pdd_refund",Long.class);
    }

    /**
     * erp无出库订单盘点入库
     * @param ritem
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> erpOrderNotFundAddStock(RefundPddEntity ritem){
        var stocks = jdbcTemplate.query("SELECT A.*  FROM erp_goods_stock_info A WHERE A.specNumber=? AND isDelete=0", new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),ritem.getSkuNumber());

        String remark="20210810前店铺"+ritem.getShopId()+"订单："+ritem.getOrder_sn()+"无出库单，退货单号："+ritem.getId();
        if(stocks.size()==0){
            String erpGoodsBadStockSQL = "INSERT INTO " + Tables.ErpGoodsBadStock + " SET quantity=?,type=3 ,specNumber=? ,locationId=? ,goodsId=? ,specId=?,reason=?,createTime=?,sourceId=?";

            var sku = jdbcTemplate.query("select * from " + Tables.ErpGoodsSpec + " WHERE specNumber=? limit 1", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), ritem.getSkuNumber());
            if(sku.size()==0)return new ResultVo<>(EnumResultVo.ParamsError, "商品在仓库查询不到");
            try {
                jdbcTemplate.update(erpGoodsBadStockSQL,ritem.getQuantity(),ritem.getSkuNumber(),0,sku.get(0).getGoodsId(),sku.get(0).getId(),remark,System.currentTimeMillis() / 1000,ritem.getId());

                jdbcTemplate.update("update dc_pdd_refund set auditStatus=1 where id=?", ritem.getId());
                return new ResultVo<>(EnumResultVo.SUCCESS, "已成功加入不良品仓");
            }catch (Exception e){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return new ResultVo<>(EnumResultVo.Fail, "加入不良品异常:"+e.getMessage());
            }
        }

        var goodStock=stocks.get(0);
        Integer locationId =  goodStock.getLocationId();//当前要入库的仓位

        Integer isExit = jdbcTemplate.queryForObject("SELECT count(0) from erp_stocktaking_invoice WHERE billNo=?",Integer.class,ritem.getId().toString());
        if(isExit>0)return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");

        Long invoiceId =0L;
        String billNo=ritem.getId().toString();
        try {
            String sql = "INSERT INTO " + Tables.ErpStocktakingInvoice + " (billNo,billDate,userId,userName,locationId,createTime,status,description) VALUE (?,?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1,billNo);
                    ps.setObject(2, DateUtil.getCurrentDate());
                    ps.setInt(3, 1);
                    ps.setString(4, "admin");
                    ps.setLong(5, locationId);
                    ps.setLong(6, System.currentTimeMillis() / 1000);
                    ps.setInt(7, ErpStocktakingInvoiceStatusEnum.SUCCESS.getIndex());
                    ps.setString(8,remark);
                    return ps;
                }
            }, keyHolder);

            invoiceId = keyHolder.getKey().longValue();
            String itemSQL = "INSERT INTO " + Tables.ErpStocktakingInvoiceItem + " (iid,billNo,goods_id,goods_number,goods_spec_id,goods_spec_number,locationId,currentQty,createTime,countedQty) VALUE (?,?,?,?,?,?,?,?,?,?)";
            jdbcTemplate.update(itemSQL, invoiceId, billNo, goodStock.getGoodsId(),goodStock.getGoodsNumber(),goodStock.getSpecId(), ritem.getSkuNumber(),locationId,goodStock.getCurrentQty(),System.currentTimeMillis() / 1000,goodStock.getCurrentQty()+ritem.getQuantity());


            String sql1 = "INSERT INTO " + Tables.ErpStockInForm + " (number,checkoutId,isDelete,stockInUserId,stockInUserName,invoiceId,stockInTime1,inType) VALUE (?,?,0,?,?,?,?,?)";
            String formNum = "IN-PAN" + OrderNumberUtils.getOrderIdByMinute();//入库单号

            final Long invoiceId_=invoiceId;
            KeyHolder keyHolder1 = new GeneratedKeyHolder();

            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, formNum);
                ps.setLong(2, 0);
                ps.setInt(3, 0);
                ps.setString(4, "admin");
                ps.setLong(5, invoiceId_);
                ps.setLong(6, System.currentTimeMillis() / 1000);
                ps.setInt(7, 3);//入库类型1采购入库2退货入库3盘点入库
                return ps;
            }, keyHolder1);
            //入库单id
            Long formId = keyHolder1.getKey().longValue();

            jdbcTemplate.update("INSERT INTO " + Tables.ErpStockInFormItem + " (formId,goodsId,specId,quantity,locationId,itemId) VALUE (?,?,?,?,?,?)", formId
                    , goodStock.getGoodsId(), goodStock.getSpecId(),ritem.getQuantity(),locationId,invoiceId);

            //更新仓位库存
            jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty+? WHERE id=?", ritem.getQuantity(), goodStock.getId());

            jdbcTemplate.update("INSERT INTO " + Tables.ErpGoodsStockInfoItem + " (stockInfoId,currentQty,purPrice,invoiceId,invoiceInfoId,stockInFormId,remark) VALUE (?,?,?,?,?,?,?)", goodStock.getId(),ritem.getQuantity(),new BigDecimal(0), 0, 0,formId,remark);

            //添加入库日志
            String rkRemark = "盘点入库店铺："+ritem.getShopId()+"SKU :" + ritem.getSkuNumber() + "盘点单号:" + billNo + "，退货Id：" + ritem.getId();
            jdbcTemplate.update("INSERT INTO " + Tables.ErpGoodsStockLogs + " SET " +
                            "goodsId=?,goodsNumber=?,specId=?,specNumber=?,locationId=?,quantity=?,type=?,sourceType=?,sourceId=?" +
                            ",remark=?,createTime=?,createUserId=?,createUserName=?,createOn=?,currQty=?",
                    goodStock.getGoodsId(), "", goodStock.getSpecId(), goodStock.getSpecNumber(), locationId,ritem.getQuantity(),
                    1, EnumGoodsStockLogSourceType.COUNTED.getIndex(),  ritem.getId(), rkRemark, new Date(), 0, "admin", System.currentTimeMillis() / 1000,goodStock.getCurrentQty()+ritem.getQuantity());

            jdbcTemplate.update("update dc_pdd_refund set auditStatus=1 where id=?", ritem.getId());
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功盘点入库");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "添加盘点入库单异常:"+e.getMessage());
        }

    }
}
